﻿Imports System.Data.SqlClient
Imports MappingAllEngine.MapDataset

Public Class Form1

    Public ElencoSource As collElencoSource
    Private id_type_mapping As Integer
    Public PrefixTarget As String
    Private Const TBaNAmAP As String = "BBI_MAPPING.BBI_MAPPING."
    Private _TbTarget As String
    Private _FieldTarget As String = ""
    Private _FieldTargetDesc As String = ""
    Private _ElencoTarget As String
    Private _TbSource As String
    Public NumSource As Integer

    Private Function GetTbFieldSource(ByVal pField As String) As String
        Return _TbSource & "." & pField
    End Function

    Private Sub Elabora()

        'ciclo tutti le tipologie di mapping

        'leggo la definizione

        'modifico le viste v_esplodi_s02_1 ... v_esplodi_s05_1

        'elimino tutti gli elementi aggiunti sull'ALL

        'ciclo tutti gli elementi ALL

        'aggiungo le anagrafiche nella tabella tmp per ogni idmapping trovato


        Dim tbMaps As New MapDatasetTableAdapters.ElencoTypeMappingTableAdapter
        Dim lrowsMaps As DataRowCollection = tbMaps.GetData.Rows
        Dim lsql As String


        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)
            connection.Open()
            lsql = "TRUNCATE TABLE BwAppMap.LOG_CONTRASTO_ALL "
            Dim command As New SqlCommand(lsql, connection)
            command.ExecuteNonQuery()
        End Using

        'ciclo tutti le tipologie di mapping
        For Each rMap As MapDataset.ElencoTypeMappingRow In lrowsMaps


            Dim tbDet As New MapDatasetTableAdapters.DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGTableAdapter
            Dim lRows As DataRowCollection
            Dim ldbHelp As New dbHelp
            Dim lSelectSource As String = ""
            Dim lsqlAnagrafica As String = ""

            ElencoSource = New collElencoSource

            id_type_mapping = rMap.ID_TYPE_MAPPING
            lRows = tbDet.GetData(id_type_mapping).Rows
            NumSource = lRows.Count


            Dim lcollMapping_WithAll As New collMapping_WithAll

            lblDescription.Text = "id_type_mapping = " + id_type_mapping.ToString + " | Sources = " + NumSource.ToString + " | "
            'If lRows.Count > 1 And lRows.Count < 6 And id_type_mapping = 200 Then
            If lRows.Count > 1 And lRows.Count < 6 Then
                'inizio esplosione di un tipo mapping

                Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)
                    connection.Open()
                    lsql = "TRUNCATE TABLE BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                    Dim command As New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()
                End Using




                Dim i As Integer = 0

                'leggo la definizione
                For Each r As DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGRow In lRows
                    i = i + 1
                    _FieldTarget = r.PREFIX_TARGET

                    If ldbHelp.ExistView(r.DB_NAME_SOURCE, r.SCHEMA_NAME_SOURCE & ".vm_" & r.ANA_TABLE_NAME_SOURCE) Then
                        _TbSource = r.DB_NAME_SOURCE & "." & r.SCHEMA_NAME_SOURCE & ".vm_" & r.ANA_TABLE_NAME_SOURCE
                    Else
                        _TbSource = r.DB_NAME_SOURCE & "." & r.SCHEMA_NAME_SOURCE & "." & r.ANA_TABLE_NAME_SOURCE
                    End If

                    Dim lSource As New clsSource

                    'altero la vista elenco.
                    lsql = ""

                    lsql = "SELECT CAST(" & GetTbFieldSource(r.FIELD_ANA_SOURCE) & " AS VARCHAR) AS MEM_ID,"
                    lsql = lsql & GetTbFieldSource(r.FIELD_ANA_SOURCE_DESC) & " AS DESCR"
                    lsql = lsql & " FROM " & _TbSource

                    ldbHelp.AlterView("v_ElencoSource_" & i.ToString, lsql)

                    lSource.v_ElencoSource = lsql


                    'altero la vista elenco mappato nella riga della griglia.

                    lSource.ID_TYPE_MAPPING = id_type_mapping
                    lSource.Prefix = r.FIELD_FACT_SOURCE
                    lSource.Num = i.ToString
                    ElencoSource.Add(lSource)


                Next r

                'modifico le viste v_esplodi_s02_1 ... v_esplodi_s05_1

                Dim lsVista_1_name As String = "BwAppMap.v_esplodi_s0" + lRows.Count.ToString + "_1"
                Dim lsVista_1_sql As String = ""
                'leggo la definizione della vista (o la creo)
                Select Case lRows.Count
                    Case 2
                        'lsVista_1_sql = My.Settings.Def
                        lsVista_1_sql = My.Settings.v_esplodi_s02_1
                    Case 3
                        'lsVista_1_sql = My.Settings.Def
                        lsVista_1_sql = My.Settings.v_esplodi_s03_1
                    Case 4
                        'lsVista_1_sql = My.Settings.Def
                        lsVista_1_sql = My.Settings.v_esplodi_s04_1
                    Case 5
                        'lsVista_1_sql = My.Settings.Def
                        lsVista_1_sql = My.Settings.v_esplodi_s05_1
                End Select

                'ora devo replicare i nomi dei fact source


                For Each lsource In ElencoSource
                    lsVista_1_sql = lsVista_1_sql.Replace("'S" + lsource.Num.ToString + "'", "'" + lsource.Prefix + "'")

                Next
                lsVista_1_sql = lsVista_1_sql.Replace("= - 1", "= " + id_type_mapping.ToString)
                ldbHelp.CreateView(lsVista_1_name + "_p", lsVista_1_sql)
                lsVista_1_sql = lsVista_1_sql.Replace("BBI_MAPPING", "BwAppMap")
                ldbHelp.CreateView(lsVista_1_name + "_p_check", lsVista_1_sql)

                'ho modificato le viste.
                'elimino tutti gli elementi aggiunti sull'ALL


                For Each lsource In ElencoSource

                    'trovo tutti gli elementi all
                    lsql = ""
                    lsql = "SELECT ID_MAPPING, value_target FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " WHERE ID_TYPE_MAPPING = " + id_type_mapping.ToString
                    lsql = lsql + " AND FIELD_SOURCE = '" + lsource.Prefix + "'"
                    lsql = lsql + " AND VALUE_SOURCE = '(ALL)'"


                    Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


                        Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)
                        connection.Open()
                        Dim tb As DataSet = New DataSet
                        adapter.Fill(tb, "ciccio")
                        For Each r As DataRow In tb.Tables("ciccio").Rows
                            'elimino tutti gli altri record tranne (all)

                            lsql = "delete FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                            lsql = lsql + " WHERE ID_TYPE_MAPPING = " + id_type_mapping.ToString
                            lsql = lsql + " AND FIELD_SOURCE = '" + lsource.Prefix + "'"
                            lsql = lsql + " AND VALUE_SOURCE <> '(ALL)'"
                            lsql = lsql + " AND ID_MAPPING = " + r("ID_MAPPING").ToString

                            Dim command As New SqlCommand(lsql, connection)
                            command.ExecuteNonQuery()

                        Next


                        For Each r As DataRow In tb.Tables("ciccio").Rows

                            Dim lMapping_WithAll As clsMapping_WithAll = Nothing
                            If Not lcollMapping_WithAll.TryGetValue(r("ID_MAPPING").ToString, lMapping_WithAll) Then
                                lMapping_WithAll = New clsMapping_WithAll(ElencoSource, r("ID_MAPPING").ToString, r("value_target").ToString)
                                lcollMapping_WithAll.Add(r("ID_MAPPING").ToString, lMapping_WithAll)
                            End If
                            lMapping_WithAll.SetWithAll(lsource.Num)

                        Next

                    End Using



                Next

                'a questo punto ho la collezione e posso inserire gli elementi nelle 3 tabelle temporanee per il prodotto cartesiano

                For Each lMapping_WithAll As clsMapping_WithAll In lcollMapping_WithAll.Values
                    Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)
                        connection.Open()

                        For Each lsource As clsSource In lMapping_WithAll.mElencoSource
                            lsql = "TRUNCATE TABLE BwAppMap.tmp_S" + lsource.Num
                            Dim command As New SqlCommand(lsql, connection)
                            command.ExecuteNonQuery()


                            'ORA INSERISCO nelle tabelle temporanee per poi fare un prodotto cartesiano.
                            lsql = "INSERT INTO BwAppMap.tmp_S" + lsource.Num

                            If lsource.ExistAll Then
                                'inserisco tutta l'anagrafica
                                lsql = lsql + " SELECT DISTINCT MEM_ID FROM (" + lsource.v_ElencoSource + ") A"
                            Else
                                lsql = lsql + " SELECT DISTINCT VALUE_SOURCE FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                                lsql = lsql + " WHERE ID_TYPE_MAPPING = " + lsource.ID_TYPE_MAPPING.ToString
                                lsql = lsql + " AND ID_MAPPING = " + lMapping_WithAll.mid_mapping.ToString
                                lsql = lsql + " AND FIELD_SOURCE = '" + lsource.Prefix + "'"


                            End If
                            command = New SqlCommand(lsql, connection)
                            command.ExecuteNonQuery()



                        Next

                        'ORA FACCIO IL PRODOTTO CARTESIANO delle dimensioni
                        Dim lsVistaProdotto As String

                        lsVistaProdotto = "BwAppMap.v_ProdottoSource_" + ElencoSource.Count.ToString

                        'trovo le combinazioni mancanti
                        Dim lsVistaNotPresente As String

                        lsVistaNotPresente = "BwAppMap.v_source_not_present_" + ElencoSource.Count.ToString

                        'utilizzo questa query per inserire nel mapping tutti i distinct source dell'elemento ALL che danno una combinazione mancanta
                        'per fare questo riciclo la collezione 

                        For Each lsource As clsSource In lMapping_WithAll.mElencoSource



                            'ORA INSERISCO nelle tabelle temporanee per poi fare un prodotto cartesiano.
                            lsql = "INSERT INTO BwAppMap.tmp_S" + lsource.Num.ToString

                            If lsource.ExistAll Then

                                lsql = " insert into BwAppMap.DATA_DWH_MODEL_ANA_MAPPING Select distinct"
                                lsql = lsql + " 144 as appl_id "
                                lsql = lsql + " , " + lsource.ID_TYPE_MAPPING.ToString + " as id_type_mapping"
                                lsql = lsql + " , '" + lsource.Prefix + "' as field_source"
                                'lsql = lsql + " , s1 as value_source"
                                lsql = lsql + " , s" + lsource.Num.ToString + " as value_source"
                                lsql = lsql + " , '" + _FieldTarget + "' as prefix_target"
                                lsql = lsql + " , '" + lMapping_WithAll.mValue_target.ToString + "' as value_target  "
                                lsql = lsql + " , " + lMapping_WithAll.mid_mapping.ToString + " as id_mapping  "
                                lsql = lsql + " from " + lsVistaNotPresente

                                Dim command As New SqlCommand(lsql, connection)
                                command.ExecuteNonQuery()

                            End If



                        Next lsource 'fine ciclo dei source sull'id mapping con elemento ALL
                    End Using
                Next lMapping_WithAll 'fine del ciclo degli id con mapping
                'a questo punto devo
                '1 


                Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)
                    connection.Open()

                    lsql = " Update BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " set APPL_ID = 0"

                    Dim command As New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()


                    lsql = " insert into BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"

                    lsql = lsql + " select * from BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " where ID_MAPPING in ("
                    lsql = lsql + " select distinct id_mapping "
                    lsql = lsql + " from BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " )"
                    lsql = lsql + " and ID_TYPE_MAPPING = " + id_type_mapping.ToString

                    command = New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()

                    'a questo punto creo la il check
                    Dim lselect As String = " "
                    Dim lgroup As String = " "

                    For Each lsource As clsSource In ElencoSource

                        lgroup = lgroup + " S" + lsource.Num.ToString + ","

                    Next
                    lselect = lgroup
                    lgroup = lgroup.Substring(0, lgroup.Length - 1)

                    For p As Integer = ElencoSource.Count + 1 To 5
                        lselect = lselect + "'' as S" + p.ToString + ","
                    Next

                    lselect = lselect.Substring(0, lselect.Length - 1)
                    lsql = " SELECT " + lselect + ", 144 as appl_id, " + id_type_mapping.ToString + " as id_type_mapping FROM " + lsVista_1_name + "_p_check"
                    lsql = lsql + " GROUP BY " + lgroup
                    lsql = lsql + " HAVING      (COUNT(ID_MAPPING) > 1)"

                    'inserisco nel log
                    Dim lsqlLog As String = ""

                    lsqlLog = "INSERT INTO BwAppMap.LOG_CONTRASTO_ALL  " + lsql
                    command = New SqlCommand(lsqlLog, connection)
                    command.ExecuteNonQuery()


                    'ORA DEVO CREARMI IL DATASET ED ELIMINARE 
                    Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)
                    Dim tb As DataSet = New DataSet
                    adapter.Fill(tb, "ciccio")
                    For Each r As DataRow In tb.Tables("ciccio").Rows
                        For Each lsource As clsSource In ElencoSource
                            'vado ad eliminare dalla tabella
                            lsql = "DELETE FROM BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                            lsql = lsql + " WHERE FIELD_SOURCE = '" + lsource.Prefix + "'"
                            lsql = lsql + " AND VALUE_SOURCE = '" + r("S" + lsource.Num.ToString).ToString + "'"
                            command = New SqlCommand(lsql, connection)
                            command.ExecuteNonQuery()
                        Next
                    Next

                    lsql = " DELETE FROM  BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " WHERE APPL_ID <> 0"

                    command = New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()

                    lsql = " update BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " set APPL_ID = 144"

                    command = New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()

                    lsql = " INSERT INTO BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " SELECT * FROM  BwAppMap.DATA_DWH_MODEL_ANA_MAPPING"

                    command = New SqlCommand(lsql, connection)
                    command.ExecuteNonQuery()




                End Using


            End If





        Next


        End

    End Sub

    Private Sub Form1_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
        Elabora()
    End Sub
End Class
